if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vw_LotNo]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vw_LotNo]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.vw_LotNo
AS
SELECT     Invoice_initem.LotNo, Invoice_initem.ItemID, vw_Items.ItemCode, vw_Items.ItemName, vw_Items.GroupCode, vw_Items.GroupName, Invoice_initem.VarietyID, 
                      Varietys.VarietyCode, Varietys.VarietyName, Invoice_initem.Qty AS inQty, 0 AS outQty, Invoice_initem.GrossWT AS inGrossWT, 0 AS outGrossWT, 
                      Invoice_initem.NetWT AS inNetWT, 0 AS outNetWT, Invoice_initem.DRemarks
FROM         Invoice_initem INNER JOIN
                      Invoice_in ON Invoice_initem.Inv_inID = Invoice_in.Inv_inID INNER JOIN
                      Varietys ON Invoice_initem.VarietyID = Varietys.VarietyID INNER JOIN
                      vw_Items ON Invoice_initem.ItemID = vw_Items.ItemID
UNION ALL
SELECT     Invoice_outitem.LotNo, Invoice_outitem.ItemID, vw_Items.ItemCode, vw_Items.ItemName, vw_Items.GroupCode, vw_Items.GroupName, Invoice_outitem.VarietyID, 
                      Varietys.VarietyCode, Varietys.VarietyName, 0 AS inQty, Invoice_outitem.Qty AS outQty, 0 AS inGrossWT, Invoice_outitem.GrossWT AS outGrossWT, 0 AS inNetWT, 
                      Invoice_outitem.NetWT AS outNetWT, Invoice_outitem.DRemarks
FROM         Invoice_outitem INNER JOIN
                      Invoice_out ON Invoice_outitem.Inv_outID = Invoice_out.Inv_outID INNER JOIN
                      Varietys ON Invoice_outitem.VarietyID = Varietys.VarietyID INNER JOIN
                      vw_Items ON Invoice_outitem.ItemID = vw_Items.ItemID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

---------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_CTN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_CTN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_CTNitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_CTNitem]
GO

CREATE TABLE [dbo].[Invoice_CTN] (
	[Inv_CTNID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_CTNDate] [datetime] NOT NULL ,
	[Inv_CTNVNo] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SessionID] [smallint] NOT NULL ,
	[Remarks] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReadOnly] [bit] NOT NULL ,
	[LoginID] [int] NOT NULL ,
	[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDateTime] [datetime] NULL ,
	[ModifyID] [int] NULL ,
	[ModifyHostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifyDateTime] [datetime] NULL ,
	[VehNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SumTop] [numeric](15, 4) NOT NULL ,
	[SumBot] [numeric](15, 4) NOT NULL ,
	[SumExAmount] [numeric](15, 4) NOT NULL ,
	[SumStAmount] [numeric](15, 4) NOT NULL ,
	[SumCrAmount] [numeric](15, 4) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_CTNitem] (
	[Inv_CTNitemID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_CTNID] [bigint] NOT NULL ,
	[PartyID] [int] NULL ,
	[VarietyID] [smallint] NULL ,
	[Tops] [numeric](15, 4) NOT NULL ,
	[Bot] [numeric](15, 4) NOT NULL ,
	[TopRate] [numeric](15, 4) NOT NULL ,
	[BotRate] [numeric](15, 4) NOT NULL ,
	[ExRate] [numeric](15, 4) NOT NULL ,
	[StRate] [numeric](15, 4) NOT NULL ,
	[ExAmount] [numeric](15, 4) NOT NULL ,
	[StAmount] [numeric](15, 4) NOT NULL ,
	[CrAmount] [numeric](15, 4) NOT NULL ,
	[Type] [tinyint] NOT NULL ,
	[DRemarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice_CTN] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_CTN] PRIMARY KEY  CLUSTERED 
	(
		[Inv_CTNID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_CTNitem] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_CTNitem] PRIMARY KEY  CLUSTERED 
	(
		[Inv_CTNitemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_CTN] ADD 
	CONSTRAINT [DF_Invoice_CTN_ReadOnly] DEFAULT (0) FOR [ReadOnly],
	CONSTRAINT [DF_Invoice_CTN_LoginID] DEFAULT (1) FOR [LoginID],
	CONSTRAINT [DF_Invoice_CTN_HostName] DEFAULT (host_name()) FOR [HostName],
	CONSTRAINT [DF_Invoice_CTN_EntryDateTime] DEFAULT (getdate()) FOR [EntryDateTime],
	CONSTRAINT [DF_Invoice_CTN_SumTop] DEFAULT (0) FOR [SumTop],
	CONSTRAINT [DF_Invoice_CTN_SumBot] DEFAULT (0) FOR [SumBot],
	CONSTRAINT [DF_Invoice_CTN_SumExAmount] DEFAULT (0) FOR [SumExAmount],
	CONSTRAINT [DF_Invoice_CTN_SumStAmount] DEFAULT (0) FOR [SumStAmount],
	CONSTRAINT [DF_Invoice_CTN_SumCrAmount] DEFAULT (0) FOR [SumCrAmount]
GO

ALTER TABLE [dbo].[Invoice_CTNitem] ADD 
	CONSTRAINT [DF_Invoice_CTNitem_Top] DEFAULT (0) FOR [Tops],
	CONSTRAINT [DF_Invoice_CTNitem_Bot] DEFAULT (0) FOR [Bot],
	CONSTRAINT [DF_Invoice_CTNitem_TopRate] DEFAULT (0) FOR [TopRate],
	CONSTRAINT [DF_Invoice_CTNitem_BotRate] DEFAULT (0) FOR [BotRate],
	CONSTRAINT [DF_Invoice_CTNitem_ExRate] DEFAULT (0) FOR [ExRate],
	CONSTRAINT [DF_Invoice_CTNitem_StRate] DEFAULT (0) FOR [StRate],
	CONSTRAINT [DF_Invoice_CTNitem_ExAmount] DEFAULT (0) FOR [ExAmount],
	CONSTRAINT [DF_Invoice_CTNitem_StAmount] DEFAULT (0) FOR [StAmount],
	CONSTRAINT [DF_Invoice_CTNitem_CrAmount] DEFAULT (0) FOR [CrAmount],
	CONSTRAINT [DF_Invoice_CTNitem_Type] DEFAULT (0) FOR [Type]
GO